SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xz_Mailings]') AND type in (N'U'))
BEGIN
CREATE TABLE [xz_Mailings](
	[MailingId] [int] IDENTITY(1,1) NOT NULL,
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) NOT NULL,
	[Subject] [nvarchar](256) NOT NULL,
	[PlainTextBody] [ntext] NOT NULL,
	[HtmlBody] [ntext] NOT NULL,
 CONSTRAINT [PK_xz_Mailings] PRIMARY KEY CLUSTERED 
(
	[MailingsId] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Mailings_InsertMailing]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_Mailings_InsertMailing]
(
    @ApplicationName	nvarchar(256),
	@AddedDate			datetime,
	@AddedBy			nvarchar(256),
	@Subject			nvarchar(256),
	@PlainTextBody		ntext,
	@HtmlBody			ntext,
	@MailingId			uniqueidentifier OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	SET NOCOUNT ON

    IF( @MailingId IS NULL )
        SELECT @MailingId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT MailingId FROM xz_Mailings
                    WHERE MailingId = @MailingId ) )
            RETURN -1
    END
   
	INSERT INTO xz_Mailings
	   (ApplicationId, MailingId, AddedDate, AddedBy, Subject, PlainTextBody, HtmlBody)
	   VALUES (@ApplicationId, @MailingId, @AddedDate, @AddedBy, @Subject, @PlainTextBody, @HtmlBody)

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Mailings_UpdateMailing]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_Mailings_UpdateMailing]
(
    @ApplicationName	nvarchar(256),
	@MailingID			uniqueidentifier,
	@Subject			nvarchar(256),
	@PlainTextBody		ntext,
	@HtmlBody			ntext
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_Mailings
	   SET Subject = @Subject, PlainTextBody = @PlainTextBody, HtmlBody = @HtmlBody
	   WHERE MailingId = @MailingId	

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Mailings_DeleteMailing]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_Mailings_DeleteMailing]
(
    @ApplicationName	nvarchar(256),
	@MailingId			uniqueidentifier
)
AS
BEGIN
    SELECT  @MailingId = m.MailingId
    FROM    [xz_Mailings] m, aspnet_Applications a
    WHERE   m.MailingId = @MailingId
        AND m.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@MailingId IS NULL)
        RETURN 0

	DELETE xz_Mailings WHERE MailingId = @MailingId
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Mailings_GetMailingById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_Mailings_GetMailingByID]
(
    @ApplicationName	nvarchar(256),
    @MailingId			uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT MailingID, AddedDate, AddedBy, Subject, PlainTextBody, HtmlBody
		FROM xz_Mailings
		WHERE MailingId = @MailingId
			AND ApplicationId = @ApplicationId
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Mailings_GetMailings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_Mailings_GetMailings]
(
    @ApplicationName	nvarchar(256),
	@ToDate				datetime
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT MailingId, AddedDate, AddedBy, Subject
	   FROM xz_Mailings
	   WHERE AddedDate <= @ToDate AND ApplicationId = @ApplicationId
	   ORDER BY AddedDate DESC

END
' 
END
